#student information online managing system
import pymysql

class studb:
	"""
	define class stu
	"""
	def __init__(self):
		'''open SQL'''
		self.db=pymysql.connect(host='localhost',user='root',password='',charset='utf8')
		self.cursor=self.db.cursor()
		try:
			#check if namesake database exists
			self.cursor.execute('show databases;')
			for i in self.cursor.fetchall():
				if i==('stu',):##why is there ','?
					if input('Delete and recreate it? (Y/N)')=='Y':
						self.cursor.execute('drop database stu;')
			self.cursor.execute('create database stu;')
			self.cursor.execute('use stu;')
			#check if namesake table exists
			self.cursor.execute("show tables like 'stume';")
			if self.cursor.rowcount==0:
				self.cursor.execute('create table stume(id int unsigned not null primary key auto_increment, name varchar(20) not null, age tinyint unsigned not null, classid varchar(20) not null);')
				self.cursor.execute("insert into stume values(null,'zhangsan',21,'python01'),(null,'lisi',22,'python02'),(null,'wangwu',23,'python03'),(null,'zhaoliu',24,'python04');")
			self.db.commit()
		#handle exceptation				
		except Exception as err:
			self.db.rollback()
			print('Operation Failed! Cause:',err)
	
	def findall(self):
		'''data query'''
		try:
			self.cursor.execute('select * from stume;')
			if self.cursor.rowcount==0:
				print('===========No student information founded!===========')
			else:
				print('|{0:<5}|{1:<10}|{2:<5}|{3:<10}'.format('sid','name','age','classid'))
				print('-'*40)
				for i in self.cursor.fetchall():
					print('|{0:<5}|{1:<10}|{2:<5}|{3:<10}'.format(i[0],i[1],i[2],i[3]))
		except Exception as err:
			self.db.rollback()
			print('Query failed! Cause:', err)


	def insert(self,stu):
		'''data insert'''
		try:
			self.cursor.execute("insert into stume values(null,'{}','{}','{}');".format(stu['name'],stu['age'],stu['classid']))
		except Exception as err:
			self.db.rollback()
			print('Insert failed! Cause:',err)

	def delete(self,sid):
		'''data detele'''
		try:
			self.cursor.execute('delete from stume where id={};'.format(sid))
		except Exception as err:
			self.db.rollback()
			print('Delete failed! Cause:',err)

	def __del__(self):
		'''close SQL'''
		self.cursor.close()
		self.db.close()


#test

#create class object
studb=studb()

#main loop
while True:
	#print origin interface
	print('='*12,'student information managing system','='*14)
	print('{0:1}|{1:30}|{2:30}'.format('','1.Query student infromation','2.Add student infromation'))
	print('{0:1}|{1:30}|{2:30}'.format('','3.Delete student information','4.Quit system'))
	print('='*40)
	key=input('Please input your choice:')
	#operate according to inputs from the keyboard
	if key=='1':
		print('='*12,'student information quering','='*14)
		studb.findall()
		input('Press Enter to continue:')
	elif key=='2':
		print('='*12,'student information adding','='*14)
		stu={}
		stu['name']=input('Please input added name:')
		stu['age']=input('Please input added age:')
		stu['classid']=input('Please input added classid:')
		studb.insert(stu)
		studb.findall()
		input('Press Enter to continue:')
	elif key=='3':
		print('='*12,'student information deleting','='*14)
		studb.findall()
		sid=input('Please input deleted id:')
		studb.delete(sid)
		studb.findall()
		input('Press Enter to continue:')
	elif key=='4':
		print('='*12,'Bye Bye','='*14)
		break
	else:
		print('======Invalid input!======')